﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using OgilvyOne.IDAL;
using OgilvyOne.Model;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class CmsArticleFront : ICmsArticleFront
    {

        private const string GET_ARTICLE_SQL = "select articleid,columnid,title,content,addtime,starttime,endtime,articletype,titleimg,author,authoremail,indextop,sorttop,istouch,templateid,summary,source,sourceurl,linkpath,tag,articletitle,articlekeyword,articledesc,isstaticpub,staticpagename,modifytime from cms_article_front where articleid=@articleid";
        public CmsArticleModel getCmsArticle(int aid)
        {
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@articleid", SqlDbType.Int, 4)
            };
            parms[0].Value = aid;
            CmsArticleModel model = null;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GET_ARTICLE_SQL, parms))
            {
                if (rdr.Read())
                {
                    model = new CmsArticleModel();
                    model.Articleid = rdr.GetInt32(0);
                    model.Columnid = rdr.GetInt32(1);
                    if (!rdr.IsDBNull(2)) model.Title = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) model.Content = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) model.Addtime = rdr.GetDateTime(4);
                    if (!rdr.IsDBNull(5)) model.Starttime = rdr.GetDateTime(5);
                    if (!rdr.IsDBNull(6)) model.Endtime = rdr.GetDateTime(6);
                    if (!rdr.IsDBNull(7)) model.Articletype = rdr.GetString(7);
                    //if (!rdr.IsDBNull(8)) model.Titleimg = rdr.GetString(8);
                    if (!rdr.IsDBNull(8))
                    {//题头图
                        //model.Titleimg = rdr.GetString(8);
                        IList imgs = GetImgs(model.Articleid, CmsConstants.IMG_TYPE_TITLE);
                        if (imgs.Count > 0)
                        {
                            CmsArticleImgModel img = (CmsArticleImgModel)imgs[0];
                            model.Titleimg = img.ImgFileName;
                        }
                    }
                    if (!rdr.IsDBNull(9)) model.Author = rdr.GetString(9);
                    if (!rdr.IsDBNull(10)) model.Authoremail = rdr.GetString(10);
                    if (!rdr.IsDBNull(11)) model.Indextop = rdr.GetString(11);
                    if (!rdr.IsDBNull(12)) model.Sorttop = rdr.GetString(12);
                    if (!rdr.IsDBNull(13)) model.Istouch = rdr.GetString(13);
                    if (!rdr.IsDBNull(14)) model.Templateid = rdr.GetInt32(14);
                    if (!rdr.IsDBNull(15)) model.Summary = rdr.GetString(15);
                    if (!rdr.IsDBNull(16)) model.Source = rdr.GetString(16);
                    if (!rdr.IsDBNull(17)) model.Sourceurl = rdr.GetString(17);
                    if (!rdr.IsDBNull(18)) model.Linkpath = rdr.GetString(18);
                    if (!rdr.IsDBNull(19)) model.Tag = rdr.GetString(19);
                    if (!rdr.IsDBNull(20)) model.Articletitle = rdr.GetString(20);
                    if (!rdr.IsDBNull(21)) model.Articlekeyword = rdr.GetString(21);
                    if (!rdr.IsDBNull(22)) model.Articledesc = rdr.GetString(22);
                    if (!rdr.IsDBNull(23)) model.Isstaticpub = rdr.GetString(23);
                    if (!rdr.IsDBNull(24)) model.Staticpagename = rdr.GetString(24);

                    /*string expert_get_sql = "select EXPERT_ID from COFCO_EXPERT_ARTICLE where ARTICLE_ID=" + aid;
                    using (SqlDataReader rdr2 = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, expert_get_sql, new SqlParameter[] { }))
                    {
                        if (rdr2.Read())
                        {
                            model.ExpertId = rdr2.GetInt32(0);
                        }
                    }*/
                    string more_get_sql = "select ARTICLE_ID,CONCACT_INFO,BRANDS,PRODUCTS,MORES,SUBTITLE,SUBTITLE2,SUBSIDIARY from CMS_ARTICLE_MORE where ARTICLE_ID=" + aid;
                    using (SqlDataReader rdr3 = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, more_get_sql, new SqlParameter[] { }))
                    {
                        if (rdr3.Read())
                        {
                            CmsArticleMore more = new CmsArticleMore();
                            more.Articleid = rdr3.GetInt32(0);
                            if (!rdr3.IsDBNull(1)) more.Concactinfo = rdr3.GetString(1);
                            if (!rdr3.IsDBNull(2)) more.Brands = rdr3.GetString(2);
                            if (!rdr3.IsDBNull(3)) more.Products = rdr3.GetString(3);
                            if (!rdr3.IsDBNull(4)) more.Mores = rdr3.GetString(4);
                            if (!rdr3.IsDBNull(5)) more.Subtitle = rdr3.GetString(5);
                            if (!rdr3.IsDBNull(6)) more.Subtitle2 = rdr3.GetString(6);
                            if (!rdr3.IsDBNull(7)) more.Subsidiary = rdr3.GetString(7);
                            model.Mores = more;
                        }
                    }

                }
            }
            return model;
        }
        /// <summary>
        /// 文章更多信息
        /// </summary>
        /// <param name="ail"></param>
        /// <returns></returns>
        public CmsArticleMore GetArticleMore(int aid)
        {
            string more_get_sql = "select ARTICLE_ID,CONCACT_INFO,BRANDS,PRODUCTS,MORES,SUBTITLE,SUBTITLE2,SUBSIDIARY from CMS_ARTICLE_MORE where ARTICLE_ID=" + aid;
            CmsArticleMore  more = null;
            using (SqlDataReader rdr3 = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, more_get_sql, new SqlParameter[] { }))
            {
                if (rdr3.Read())
                {
                    more = new CmsArticleMore();
                    more.Articleid = rdr3.GetInt32(0);
                    if (!rdr3.IsDBNull(1)) more.Concactinfo = rdr3.GetString(1);
                    if (!rdr3.IsDBNull(2)) more.Brands = rdr3.GetString(2);
                    if (!rdr3.IsDBNull(3)) more.Products = rdr3.GetString(3);
                    if (!rdr3.IsDBNull(4)) more.Mores = rdr3.GetString(4);
                    if (!rdr3.IsDBNull(5)) more.Subtitle = rdr3.GetString(5);
                    if (!rdr3.IsDBNull(6)) more.Subtitle2 = rdr3.GetString(6);
                    if (!rdr3.IsDBNull(7)) more.Subsidiary = rdr3.GetString(7);
                }
            }
            return more;
        }

        public bool Del(string ids)
        {
            bool re = false;
            string delsql = "delete from cms_article_front where articleid in(" + ids + ")";
            int i = SQLHelper.ExecuteSql(delsql, new SqlParameter[] { });
            if (i > 0) re = true;
            return re;
        }

        public bool PushFront(CmsArticleModel cm)
        {
            if (cm.Articleid < 1) return false;
            if (IsExists(cm.Articleid))
            {//更新
                return Update(cm);
            }
            else
            {//新增
                return Insert(cm);
            }
        }

        public bool Insert(CmsArticleModel article)
        {
            bool re = false;
            string insertsql = "insert into cms_article_front(articleid,columnid,title,content,addtime,starttime,endtime,articletype,titleimg,author,authoremail,indextop,sorttop,istouch,templateid,summary,source,sourceurl,linkpath,tag,modifytime,hits,articletitle,articlekeyword,articledesc,isstaticpub,staticpagename,order_num,colls) "
                + "values(" + article.Articleid + "," + article.Columnid + ",@title,@content,getDate()," + (article.Starttime.Year < 1900 ? "null" : "'" + article.Starttime.ToString("yyyy-MM-dd") + "'") + "," + (article.Endtime.Year < 1900 ? "null" : "'" + article.Endtime.ToString("yyyy-MM-dd") + "'")
                + ",@articletype,@titleimg,@author,@authoremail,@indextop,@sorttop,@istouch,@templateid,@summary,@source,@sourceurl,@linkpath,@tag,@modifytime,0,@articletitle,@articlekeyword,@articledesc,@isstaticpub,@staticpagename,@order_num,0)";
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@title", SqlDbType.VarChar, 255),
                new SqlParameter("@content", SqlDbType.Text),
                new SqlParameter("@articletype", SqlDbType.Char,1),
                new SqlParameter("@titleimg",SqlDbType.VarChar,255),
                new SqlParameter("@author", SqlDbType.VarChar,50),
                new SqlParameter("@authoremail", SqlDbType.VarChar, 255),
                new SqlParameter("@indextop",SqlDbType.Char,1),
                new SqlParameter("@sorttop", SqlDbType.Char,1),
                new SqlParameter("@istouch", SqlDbType.Char,1),
                new SqlParameter("@templateid", SqlDbType.Int,4),
                new SqlParameter("@summary", SqlDbType.VarChar,2000),
                new SqlParameter("@source", SqlDbType.VarChar,255),
                new SqlParameter("@sourceurl", SqlDbType.VarChar, 255),
                new SqlParameter("@linkpath", SqlDbType.VarChar, 100),
                new SqlParameter("@tag", SqlDbType.VarChar, 200),
                new SqlParameter("@modifytime", SqlDbType.DateTime),
                new SqlParameter("@articletitle", SqlDbType.VarChar, 255),
                new SqlParameter("@articlekeyword",SqlDbType.VarChar, 500),
                new SqlParameter("@articledesc", SqlDbType.VarChar, 500),
                new SqlParameter("@isstaticpub", SqlDbType.Char, 1),
                new SqlParameter("@staticpagename", SqlDbType.VarChar, 100),
                new SqlParameter("@order_num", SqlDbType.Int, 4)
            };
            parms[0].Value = article.Title;
            parms[1].Value = article.Content;
            parms[2].Value = article.Articletype;
            parms[3].Value = article.Titleimg;
            parms[4].Value = article.Author;
            parms[5].Value = article.Authoremail;
            parms[6].Value = article.Indextop;
            parms[7].Value = article.Sorttop;
            parms[8].Value = article.Istouch;
            parms[9].Value = article.Templateid;
            parms[10].Value = article.Summary;
            parms[11].Value = article.Source;
            parms[12].Value = article.Sourceurl;
            parms[13].Value = article.Linkpath;
            parms[14].Value = article.Tag;
            parms[15].Value = DateTime.Now;
            parms[16].Value = article.Articletitle;
            parms[17].Value = article.Articlekeyword;
            parms[18].Value = article.Articledesc;
            parms[19].Value = article.Isstaticpub;
            parms[20].Value = article.Staticpagename;
            parms[21].Value = article.OrderNum;
            int i = SQLHelper.ExecuteSql(insertsql, parms);
            if (i > 0) re = true;
            return re;
        }

        public bool Update(CmsArticleModel article)
        {
            bool re = false;
            string updatesql = "update cms_article_front set title=@title,content=@content,starttime=" + (article.Starttime.Year < 1900 ? "null" : "'" + article.Starttime.ToString("yyyy-MM-dd") + "'") + ""
                + ",endtime=" + (article.Endtime.Year < 1900 ? "null" : "'" + article.Endtime.ToString("yyyy-MM-dd") + "'") + ",articletype=@articletype,titleimg=@titleimg,author=@author,authoremail=@authoremail,indextop=@indextop,sorttop=@sorttop,istouch=@istouch,templateid=@templateid,summary=@summary,"
                + "source=@source,sourceurl=@sourceurl,linkpath=@linkpath,tag=@tag,modifytime=getDate(),articletitle=@articletitle,articlekeyword=@articlekeyword,articledesc=@articledesc,isstaticpub=@isstaticpub,staticpagename=@staticpagename,order_num=@order_num where articleid=" + article.Articleid;
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@title", SqlDbType.VarChar, 255),
                new SqlParameter("@content", SqlDbType.Text),
                new SqlParameter("@articletype", SqlDbType.Char,1),
                new SqlParameter("@titleimg",SqlDbType.VarChar,255),
                new SqlParameter("@author", SqlDbType.VarChar,50),
                new SqlParameter("@authoremail", SqlDbType.VarChar, 255),
                new SqlParameter("@indextop",SqlDbType.Char,1),
                new SqlParameter("@sorttop", SqlDbType.Char,1),
                new SqlParameter("@istouch", SqlDbType.Char,1),
                new SqlParameter("@templateid", SqlDbType.Int,4),
                new SqlParameter("@summary", SqlDbType.VarChar,2000),
                new SqlParameter("@source", SqlDbType.VarChar,255),
                new SqlParameter("@sourceurl", SqlDbType.VarChar, 255),
                new SqlParameter("@linkpath", SqlDbType.VarChar, 100),
                new SqlParameter("@tag", SqlDbType.VarChar, 200),
                new SqlParameter("@articletitle", SqlDbType.VarChar, 255),
                new SqlParameter("@articlekeyword",SqlDbType.VarChar, 500),
                new SqlParameter("@articledesc", SqlDbType.VarChar, 500),
                new SqlParameter("@isstaticpub", SqlDbType.Char, 1),
                new SqlParameter("@staticpagename", SqlDbType.VarChar, 100),
                new SqlParameter("@order_num", SqlDbType.Int, 4)
            };
            parms[0].Value = article.Title;
            parms[1].Value = article.Content;
            parms[2].Value = article.Articletype;
            parms[3].Value = article.Titleimg;
            parms[4].Value = article.Author;
            parms[5].Value = article.Authoremail;
            parms[6].Value = article.Indextop;
            parms[7].Value = article.Sorttop;
            parms[8].Value = article.Istouch;
            parms[9].Value = article.Templateid;
            parms[10].Value = article.Summary;
            parms[11].Value = article.Source;
            parms[12].Value = article.Sourceurl;
            parms[13].Value = article.Linkpath;
            parms[14].Value = article.Tag;
            parms[15].Value = article.Articletitle;
            parms[16].Value = article.Articlekeyword;
            parms[17].Value = article.Articledesc;
            parms[18].Value = article.Isstaticpub;
            parms[19].Value = article.Staticpagename;
            parms[20].Value = article.OrderNum;
            int i = SQLHelper.ExecuteSql(updatesql, parms);
            if (i > 0) re = true;
            return re;
        }

        private const string hs = "";

        public bool addHits(int aid)
        {
            bool re = false;
            lock (hs)
            {
                
                string updatesql = "update cms_article_front set HITS=HITS+1  where articleid=" + aid;

                int i = SQLHelper.ExecuteSql(updatesql, new SqlParameter[] { });
                if (i > 0) re = true;
            }
            return re;
        }
        private const string cs = "";
        public bool addColls(int aid)
        {
            bool re = false;
            lock (cs)
            {
                
                string updatesql = "update cms_article_front set COLLS=COLLS+1  where articleid=" + aid;

                int i = SQLHelper.ExecuteSql(updatesql, new SqlParameter[] { });
                if (i > 0) re = true;
                
            }
            return re;
        }

        public int GetArticleHits(int aid)
        {
            int num = 0;
            string GetArticleHitssql = "select HITS from cms_article_front  where articleid=" + aid;
            SqlParameter[] parms = new SqlParameter[] {
                
            };
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GetArticleHitssql, parms))
            {
                if (rdr.Read())
                {
                    num = rdr.GetInt32(0);
                }
            }
            return num;
        }

        public int GetArticleColls(int aid)
        {
            int num = 0;
            string GetArticleCOLLSsql = "select COLLS from cms_article_front  where articleid=" + aid;
            SqlParameter[] parms = new SqlParameter[] {
                
            };
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, GetArticleCOLLSsql, parms))
            {
                if (rdr.Read())
                {
                    num = rdr.GetInt32(0);
                }
            }
            return num;
        }

        
        

        /// <summary>
        /// 文章是否存在
        /// </summary>
        /// <param name="aid"></param>
        /// <returns></returns>
        private bool IsExists(int aid)
        {
            string sql = "select articleid from cms_article_front where articleid=@articleid";
            bool re = false;
            SqlParameter[] parms = new SqlParameter[] {
                new SqlParameter("@articleid", SqlDbType.Int, 4)
            };
            parms[0].Value = aid;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            {
                if (rdr.Read())
                {
                    re = true;
                }
            }
            return re;
        }

        //--------------------------------------------------------------------------------------- 前台

        public DataSet GetSqlTable(string sql)
        {

            return SQLHelper.Query(sql);
        }

        private const string ACTICLE_ORDER_STR = "ORDER BY starttime desc, modifytime desc";
        private const string ACTICLE_TIPS_ORDER = " order by hits desc";

        //发布条件
        private string GetPubColl()
        {
            return "( starttime <= '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' or starttime is null)";
        }

        private const string VIDEOS_LIST_SQL = "select VIDEO_ID,VIDEO_NAME,VIDEO_FILE_NAME,VIDEO_TYPE,VIDEO_DESC,VIDEO_ORDER,ARTICLE_ID,STATE,ADD_TIME,USER_ID from CMS_ARTICLE_VIDEO where ARTICLE_ID=@ARTICLE_ID and STATE=1 order by VIDEO_ORDER,ADD_TIME desc"; //发布
        /// <summary>
        /// 文章视频列表
        /// </summary>
        /// <param name="aid"></param>
        /// <returns></returns>
        public IList GetVideos(int aid)
        {
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@ARTICLE_ID", SqlDbType.Int, 4)
                
            };
            parms[0].Value = aid;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, VIDEOS_LIST_SQL, parms))
            {
                if (rdr.Read())
                {
                    CmsArticleVideoModel mim = new CmsArticleVideoModel();
                    mim.VideoId = rdr.GetInt32(0);
                    mim.VideoName = rdr.GetString(1);
                    mim.VideoFileName = rdr.GetString(2);

                    if (!rdr.IsDBNull(3)) mim.videoType = rdr.GetInt32(3);
                    if (!rdr.IsDBNull(4)) mim.videoDesc = rdr.GetString(4);

                    mim.VideoOrder = rdr.GetInt32(5);
                    mim.ArticleId = rdr.GetInt32(6);
                    mim.State = rdr.GetInt32(7);
                    mim.Add_time = rdr.GetDateTime(8);
                    mim.UserId = rdr.GetInt32(9);

                    datalist.Add(mim);
                }
            }

            return datalist;
        }

        private const string IMGS_TYPE_LIST_SQL = "select IMGS_ID,IMG_NAME,IMG_TYPE,IMG_FILE_NAME,ARTICLE_ID,IMG_ORDER,STATE,ADD_TIME,USER_ID,IMG_SMALL_FILE_NAME,IMG_LIST_FILE_NAME from CMS_ARTICLE_IMGS where ARTICLE_ID=@ARTICLE_ID and IMG_TYPE=@IMG_TYPE and STATE=1 order by IMG_ORDER,ADD_TIME desc"; //发布
        /// <summary>
        /// 文章类型图片
        /// </summary>
        /// <param name="aid"></param>
        /// <param name="imgtype"></param>
        /// <returns></returns>
        public IList GetImgs(int aid, int imgtype)
        {

            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@ARTICLE_ID", SqlDbType.Int, 4),
                new SqlParameter("@IMG_TYPE", SqlDbType.Int, 4)
            };
            parms[0].Value = aid;
            parms[1].Value = imgtype;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, IMGS_TYPE_LIST_SQL, parms))
            {
                while (rdr.Read())
                {
                    CmsArticleImgModel mim = new CmsArticleImgModel();

                    mim = new CmsArticleImgModel();
                    mim.ImgsId = rdr.GetInt32(0);
                    mim.ImgName = rdr.GetString(1);
                    mim.ImgType = rdr.GetInt32(2);
                    mim.ImgFileName = rdr.GetString(3);

                    mim.ArticleId = rdr.GetInt32(4);
                    if (!rdr.IsDBNull(5)) mim.ImgOrder = rdr.GetInt32(5);
                    //mim.State = rdr.GetInt32(6);
                    mim.Add_time = rdr.GetDateTime(7);
                    mim.UserId = rdr.GetInt32(8);
                    if (!rdr.IsDBNull(9)) mim.ImgSmallFileName = rdr.GetString(9);
                    if (!rdr.IsDBNull(10)) mim.Img_list_file_name = rdr.GetString(10);
                    datalist.Add(mim);

                }
            }
            return datalist;
        }



        /// <summary>
        /// 文章列表
        /// </summary>
        /// <param name="cid">栏目ID</param>
        /// <param name="rows">列表数量 0 所有</param>
        /// <returns></returns>
        public IList GetArticleList(int cid, int rows)
        {
            return GetArticleList(cid, rows, ACTICLE_ORDER_STR);
        }
        public IList GetArticleOrderList(int cid, int rows)
        {
            return GetArticleList(cid, rows, " order by order_num");
        }

        public DataSet GetArticleDataSet(int cid, int rows)
        {
            return GetArticleDataSet(cid, rows, ACTICLE_ORDER_STR);
        }
        public DataSet GetArticleOrderDataSet(int cid, int rows)
        {
            return GetArticleDataSet(cid, rows, "order by order_num");
        }


        private DataSet GetArticleDataSet(int cid, int rows, string orderstr)
        {
            string top = "";
            if (rows > 0)
            {
                top = " top " + rows;
            }
            string querysql = "select " + top + " articleid,columnid,title,content,addtime,'',starttime,endtime,articletype,titleimg,author,authoremail,indextop,sorttop,istouch,templateid,summary,source,sourceurl,linkpath,tag,articletitle,articlekeyword,articledesc,isstaticpub,staticpagename,modifytime,order_num,colls,hits from cms_article_front ";
            querysql += " where " + GetPubColl() + " and columnid=" + cid + orderstr;

            return SQLHelper.Query(querysql);
        }
        private IList GetArticleList(int cid, int rows, string orderstr)
        {
            return GetArticleList(" and columnid=" + cid, rows, orderstr);
        }

        private IList GetArticleList(string andwhere, int rows, string orderstr)
        {
            string top = "";
            if (rows > 0)
            {
                top = " top " + rows;
            }
            string querysql = "select " + top + " articleid,columnid,title,content,addtime,'',starttime,endtime,articletype,titleimg,author,authoremail,indextop,sorttop,istouch,templateid,summary,source,sourceurl,linkpath,tag,articletitle,articlekeyword,articledesc,isstaticpub,staticpagename,modifytime,order_num,colls,hits from cms_article_front ";
            querysql += " where " + GetPubColl() +  andwhere;

            querysql += orderstr;
            SqlParameter[] parms = new SqlParameter[] {
                
            };

            return GetArticleList(querysql);
        }

        private IList GetArticleList(string sql)
        {
            SqlParameter[] parms = new SqlParameter[] {
                
            };

            IList datalist = new ArrayList();
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            {
                while (rdr.Read())
                {
                    CmsArticleModel model = new CmsArticleModel();
                    model.Articleid = rdr.GetInt32(0);
                    model.Columnid = rdr.GetInt32(1);
                    if (!rdr.IsDBNull(2)) model.Title = rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) model.Content = rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) model.Addtime = rdr.GetDateTime(4);
                    if (!rdr.IsDBNull(6)) model.Starttime = rdr.GetDateTime(6);
                    if (!rdr.IsDBNull(7)) model.Endtime = rdr.GetDateTime(7);
                    if (!rdr.IsDBNull(8)) model.Articletype = rdr.GetString(8);

                    if (!rdr.IsDBNull(9))
                    {//题头图
                        //model.Titleimg = rdr.GetString(9);
                        IList imgs = GetImgs(model.Articleid, CmsConstants.IMG_TYPE_TITLE);
                        if (imgs.Count > 0)
                        {
                            CmsArticleImgModel img = (CmsArticleImgModel)imgs[0];
                            model.Titleimg = img.ImgFileName;
                        }
                    }

                    if (!rdr.IsDBNull(10)) model.Author = rdr.GetString(10);
                    if (!rdr.IsDBNull(11)) model.Authoremail = rdr.GetString(11);
                    if (!rdr.IsDBNull(12)) model.Indextop = rdr.GetString(12);
                    if (!rdr.IsDBNull(13)) model.Sorttop = rdr.GetString(13);
                    if (!rdr.IsDBNull(14)) model.Istouch = rdr.GetString(14);
                    if (!rdr.IsDBNull(15)) model.Templateid = rdr.GetInt32(15);
                    if (!rdr.IsDBNull(16)) model.Summary = rdr.GetString(16);
                    if (!rdr.IsDBNull(17)) model.Source = rdr.GetString(17);
                    if (!rdr.IsDBNull(18)) model.Sourceurl = rdr.GetString(18);
                    if (!rdr.IsDBNull(19)) model.Linkpath = rdr.GetString(19);
                    if (!rdr.IsDBNull(20)) model.Tag = rdr.GetString(20);

                    if (!rdr.IsDBNull(21)) model.Articletitle = rdr.GetString(21);
                    if (!rdr.IsDBNull(22)) model.Articlekeyword = rdr.GetString(22);
                    if (!rdr.IsDBNull(23)) model.Articledesc = rdr.GetString(23);
                    if (!rdr.IsDBNull(24)) model.Isstaticpub = rdr.GetString(24);
                    if (!rdr.IsDBNull(25)) model.Staticpagename = rdr.GetString(25);
                    if (!rdr.IsDBNull(26)) model.Modifytime = rdr.GetDateTime(26);
                    if (!rdr.IsDBNull(27)) model.OrderNum = rdr.GetInt32(27);
                    if (!rdr.IsDBNull(28)) model.Colls = rdr.GetInt32(28);
                    if (!rdr.IsDBNull(29)) model.Hits = rdr.GetInt32(29);

                    if (model.Author != null && model.Author != "")
                    {//专家ID
                        string expert_get_sql = "select EXPERT_ID from COFCO_EXPERT_ARTICLE where ARTICLE_ID=" + model.Articleid;
                        using (SqlDataReader rdr2 = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, new SqlParameter[] { }))
                        {
                            if (rdr2.Read())
                            {
                                model.ExpertId = rdr2.GetInt32(0);
                            }
                        }
                    }
                    datalist.Add(model);
                }
            }
            return datalist;
        }

        public IList GetColumnTopArticleList(int cid, int rows)
        {
            return GetArticleList(" and sorttop='1' and columnid=" + cid, rows, ACTICLE_ORDER_STR);

        }
        
        public IList GetColumnTopArticleList(string layerid, int rows)
        {
            return GetArticleList(" and sorttop='1' and columnid in (select classid from cms_column where layerid like '"+layerid+"%')", rows, ACTICLE_ORDER_STR);

        }
        /// <summary>
        /// 美味人生（下部分）文章查询
        /// </summary>
        /// <returns></returns>
        public IList GetHealthArticleList(int cid, int rows)
        {
            return GetArticleList(" and sorttop='1' and (columnid = " + cid + " or columnid in (select classid from cms_column where PARENTID = " + cid + ")) ", rows, ACTICLE_ORDER_STR);
        }
        public IList GetVideoArticleList(int cid, int rows)
        {
            string top = "";
            if (rows > 0)
            {
                top = " top " + rows;
            }
            string querysql = "select " + top + " cc.articleid,cc.columnid,cc.title,cc.content,cc.addtime,'',cc.starttime,cc.endtime,cc.articletype,cc.titleimg,author,authoremail,cc.indextop,cc.sorttop,cc.istouch,cc.templateid,cc.summary,cc.source,cc.sourceurl,linkpath,cc.tag,articletitle,articlekeyword,articledesc,cc.isstaticpub,cc.staticpagename,cc.modifytime,cc.order_num,colls,hits from cms_article_front as cc,cms_column as ccc ";
            querysql += " where " + GetPubColl() + " and cc.columnid=ccc.classid and cc.sorttop='1' and (cc.columnid = " + cid + " or ccc.PARENTID = " + cid + ") order by ccc.ORDERID,cc.order_num";
            return GetArticleList(querysql);
        }
        /// <summary>
        /// 美味厨房列表页热点查询
        /// </summary>
        /// <returns></returns>
        public IList GetHealthArticleLists(int cid, int rows)
        {
            return GetArticleList(" and sorttop='1' and columnid  = " + cid, rows, ACTICLE_ORDER_STR);
        }
       
        /// <summary>
        /// 新闻列表
        /// </summary>
        /// <returns></returns>
        public IList GetNewsList(int cid, int year, int month)
        {
            IList list = GetArticleList(" and year(starttime)=" + year + " and month(starttime)=" + month + " and columnid = " + cid, 0, ACTICLE_ORDER_STR);
            if (list.Count > 0) return list;
            Hashtable t = GetArticleGroupYear(cid);
            list = new ArrayList();
            if (t.Keys.Count > 0)
            {
                foreach (object key in t.Keys)
                {
                    year = (int)key;
                    IList ml = (IList)t[key];
                    month = (int)ml[0];
                    break;
                }
                list = GetArticleList(" and year(starttime)=" + year + " and month(starttime)=" + month + " and columnid = " + cid, 0, ACTICLE_ORDER_STR);
            }
            return list;
        }

        public IList GetHomeTopArticleList(int cid, int rows)
        {
            string top = "";
            if (rows > 0)
            {
                top = " top " + rows;
            }
            string querysql = "select " + top + " articleid,columnid,title,content,addtime,'',starttime,endtime,articletype,titleimg,author,authoremail,indextop,sorttop,istouch,templateid,summary,source,sourceurl,linkpath,tag,articletitle,articlekeyword,articledesc,isstaticpub,staticpagename,modifytime,order_num,colls,hits from cms_article_front ";
            querysql += " where " + GetPubColl() + " and indextop='1' and columnid=" + cid + " and convert(nvarchar(10), starttime, 120)<=convert(nvarchar(10), getdate(), 120)";
            querysql += ACTICLE_ORDER_STR;
            return GetArticleList(querysql);

        }
        /// <summary>
        /// 关联联系信息列表
        /// </summary>
        /// <param name="aids"></param>
        /// <returns></returns>
        public IList GetConcacts(string aids)
        {
            string w = " and  articleid in (" + aids + ") ";
            return GetArticleList(w, 0, " order by order_num ");
        }

        /// <summary>
        /// 文章分页列表
        /// </summary>
        /// <param name="cid">栏目ID</param>
        /// <param name="pageon">页码</param>
        /// <param name="pagesize">页大小</param>
        /// <returns></returns>
        public PageTools GetArticlePageList(int cid, int pageno, int pagesize)
        {
            string dtop = "";
            if (cid == 38 || cid == 137 || cid == 138)
            {
                dtop = " and articleid not in (select top 4 articleid from cms_article_front where" + GetPubColl() + " and columnid =" + cid + " and sorttop='1' " + ACTICLE_ORDER_STR + ")";
            }
            return GetArticlePageList("where " + GetPubColl() + " and columnid =" + cid + dtop, ACTICLE_ORDER_STR, pageno, pagesize);
        }

        private string GetPubCollnew()
        {
            return "( starttime <= '" + DateTime.Now.ToString("yyyy-MM-dd") + "' or starttime is null)";
        }

        /// <summary>
        /// 查询今日热点分页 2012-4-24 李学明 add
        /// </summary>
        /// <param name="pageno">页码 </param>
        /// <param name="pagesize">每页的大小</param>
        /// <param name="columnid">栏目ID </param>
        /// <returns></returns>
        public PageTools GetHotspotPageList(int columnid, int pageno, int pagesize)
        {
            var where = "where " + GetPubCollnew() + " and indextop='1' and  COLUMNID=" + columnid;
            return GetArticlePageList(where, ACTICLE_ORDER_STR, pageno, pagesize);
        }

        public PageTools GetArticlePageList(CmsArticleQuery qmodel, int pageno, int pagesize)
        {
            string where = "where " + GetPubColl() + " and columnid=" + qmodel.columnid;
            string orderstr = ACTICLE_ORDER_STR;
            return GetArticlePageList(where, orderstr, pageno, pagesize);
        }

        public PageTools GetArticlePageList(string where, string orderstr, int pageno, int pagesize)
        {
            string qsql = "select articleid,columnid,title,content,addtime,'',starttime,endtime,articletype,titleimg,author,authoremail,indextop,sorttop,istouch,templateid,summary,source,sourceurl,linkpath,tag,articletitle,articlekeyword,articledesc,isstaticpub,staticpagename,modifytime,order_num,colls,hits from (SELECT * FROM (SELECT *,ROW_NUMBER() OVER (" + orderstr + ") AS RowNumber FROM cms_article_front " + where + " ) as c ";
            qsql += "WHERE RowNumber BETWEEN " + ((pageno - 1) * pagesize + 1) + " AND " + (pageno * pagesize) + ") as reslut";
            IList datalist = GetArticleList(qsql);
            int counts = 0;
            string csql = "select count(*) from cms_article_front " + where;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, csql, new SqlParameter[] { }))
            {
                if (rdr.Read())
                {
                    counts = rdr.GetInt32(0);
                }
            }
            PageTools pagetools = new PageTools(datalist, pageno, pagesize, counts);
            return pagetools;
        }

        /// <summary>
        /// 热点收藏文章分页列表
        /// </summary>
        /// <param name="layerid">栏目Layerid</param>
        /// <param name="pageno">当前页</param>
        /// <param name="pagesize">页大小</param>
        /// <returns></returns>
        public PageTools GetCollsArticlePageList(string layerid, int pageno, int pagesize)
        {
            string where = "where " + GetPubColl() + " and colls>0";
            if (layerid != null && layerid != "")
            {
                where += " and columnid in (select classid from cms_column where LAYERID like '" + StringUtil.ReplaceSecStr(layerid) + "%')";
            }
            string orderstr = " order by colls desc";
            return GetArticlePageList(where, orderstr, pageno, pagesize);
        }
        public PageTools GetCollsArticlePageList(string layerid, int pageno, int pagesize,int lid)
        {
            string where = "where " + GetPubColl();
            if (layerid != null && layerid != "")
            {
                where += " and columnid in (select classid from cms_column where LAYERID like '" + StringUtil.ReplaceSecStr(layerid) + "%')";
            }
            if (lid > 0)
            {
                where += " and ','+tag+',' like '%,"+lid+",%' ";
            }
            string orderstr = " order by colls desc";
            return GetArticlePageList(where, orderstr, pageno, pagesize);
        }
        public IList GetCollsArtticleList(string layerid, int rows)
        {
            string top = "";
            if (rows > 0)
            {
                top = " top " + rows;
            }
            string querysql = "select " + top + " articleid,columnid,title,content,addtime,'',starttime,endtime,articletype,titleimg,author,authoremail,indextop,sorttop,istouch,templateid,summary,source,sourceurl,linkpath,tag,articletitle,articlekeyword,articledesc,isstaticpub,staticpagename,modifytime,order_num,colls,hits from cms_article_front ";
            querysql += " where " + GetPubColl() + " and columnid in (select classid form cms_column where LAYERID like '" + StringUtil.ReplaceSecStr(layerid) + "%')";

            querysql += " order by colls desc";
            SqlParameter[] parms = new SqlParameter[] {
                
            };

            return GetArticleList(querysql);
        }
        /// <summary>
        /// 文章标签列表
        /// </summary>
        /// <param name="tids"></param>
        /// <returns></returns>
        public IList GetArticleTags(string tids)
        {
            
            string qsql = "select TAG_NAME,t.STATE,ADD_TIME,USER_ID,CLICK_NUM,TAG_ID,USERNAME,t.IS_HOTS,t.HOTS_TYPE,t.CLASSIDS from CMS_ARTICLE_TAG t left join cms_users u on t.USER_ID=u.aid where STATE = 1 and TAG_ID in(" + tids + ")";

            qsql += " order by t.ADD_TIME desc";
            IList datalist = new ArrayList();
            SqlParameter[] parms = new SqlParameter[] { };

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, qsql, parms))
            {
                while (rdr.Read())
                {
                    CmsArticleTagModel mim = new CmsArticleTagModel();

                    mim.TagName = rdr.GetString(0);
                    mim.State = rdr.GetInt32(1);
                    mim.AddTime = rdr.GetDateTime(2);
                    mim.UserId = rdr.GetInt32(3);
                    mim.ClickNum = rdr.GetInt32(4);
                    mim.TagId = rdr.GetInt32(5);
                    if (!rdr.IsDBNull(6)) mim.UserName = rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mim.IsHots = rdr.GetInt32(7);
                    if (!rdr.IsDBNull(8)) mim.HotsType = rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) mim.ClassIds = rdr.GetString(9);
                    datalist.Add(mim);

                }
            }
            return datalist;
        }
        /// <summary>
        /// 营养专家
        /// </summary>
        /// <returns></returns>
        public IList GetHealth(string where)
        {
            string sql = "select EXPERT_NAME,EXPERT_JOB,EXPERT_MOTTO,EXPERT_SPEC,EXPERT_UNIT,EXPERT_DESC,EXPERT_PHOTO,EXPERT_ID,EXPERT_LIST_IMG_FILE_NAME from COFCO_HEALTH_EXPERT where state = 1 ";
            if (where != null && where.Trim() != "")
            {
                sql += " and EXPERT_ID= '" + where + "'";
            }
            IList datalist = new ArrayList();
            //SqlParameter[] parms = new SqlParameter[] { };

            //using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            //{
            //    while (rdr.Read())
            //    {
            //        HealthExpertModel hem = new HealthExpertModel();

            //        if (!rdr.IsDBNull(0)) hem.ExpertName = rdr.GetString(0);
            //        if (!rdr.IsDBNull(1)) hem.ExpertJob = rdr.GetString(1);
            //        if (!rdr.IsDBNull(2)) hem.ExpertMotto = rdr.GetString(2);
            //        if (!rdr.IsDBNull(3)) hem.ExpertSpec = rdr.GetString(3);
            //        if (!rdr.IsDBNull(4)) hem.ExpertUnit = rdr.GetString(4);
            //        if (!rdr.IsDBNull(5)) hem.ExpertDesc = rdr.GetString(5);
            //        if (!rdr.IsDBNull(6)) hem.ExpertPhoto = rdr.GetString(6);
            //        if (!rdr.IsDBNull(7)) hem.ExpertId = rdr.GetInt32(7);
            //        if (!rdr.IsDBNull(8)) hem.ExpertListImg = rdr.GetString(8);

            //        datalist.Add(hem);

            //    }
            //}
            return datalist;
        }
        /// <summary>
        /// 专家文章列表
        /// </summary>
        /// <returns></returns>
        public IList GetHealthArticle(int expid)
        {
            return GetArticleList(" and sorttop='1' and ARTICLEID in (select b.ARTICLE_ID from COFCO_EXPERT_ARTICLE b where b.EXPERT_ID=" + expid + ")", 14, ACTICLE_ORDER_STR);
        }
        /// <summary>
        /// 食谱内容页-相关食谱列表
        /// </summary>
        /// <returns></returns>
        public IList GetRecipeArticle(string where,int cid)
        {
            return GetArticleList(" and " + where + " and  COLUMNID = "+cid, 4, " order by tag desc");
        }
       
        /// <summary>
        /// 当前栏目人气文章列表
        /// </summary>
        /// <returns></returns>
        public IList GetHealthArticleTop(int cid, int rows)
        {
            return GetArticleList(" and COLUMNID = " + cid.ToString(), rows, "order by colls desc,starttime desc");
        }
        /// <summary>
        /// 健康学院首页热门收藏文章列表
        /// </summary>
        /// <returns></returns>
        public IList GetHealthArticleTop(string layerid, int top)
        {
            return GetArticleList(" and  COLUMNID in (select classid from cms_column where LAYERID like '" + layerid + "%')", top, "order by colls desc");
        }
        /// <summary>
        /// 获取联系信息
        /// </summary>
        /// <returns></returns>
        public IList GetContactInformation(int aid)
        {
            return GetArticleList(" select top 1 articleid,columnid,title,content,addtime,'',starttime,endtime,articletype,titleimg,author,authoremail,indextop,sorttop,istouch,templateid,summary,source,sourceurl,linkpath,tag,articletitle,articlekeyword,articledesc,isstaticpub,staticpagename,modifytime,order_num,colls,hits from cms_article_front where ARTICLEID = " + aid);
        }
        /// <summary>
        /// 层级所有文章列表
        /// </summary>
        /// <param name="layerid"></param>
        /// <param name="row"></param>
        /// <returns></returns>
        public IList GetLayerArticleList(string layerid, int row)
        {
            return GetArticleList(" and  COLUMNID in (select classid from cms_column where LAYERID like '" + layerid + "%')", row, ACTICLE_ORDER_STR);
        }

        public IList GetHoemLayerArticleList(string layerid, int row)
        {
            return GetArticleList(" and indextop='1' and COLUMNID in (select classid from cms_column where LAYERID like '" + layerid + "%')", row, ACTICLE_ORDER_STR);
        }

        public Hashtable GetArticleGroupYear(int cid)
        {

            Hashtable map = new Hashtable();
            string sql = "select year(starttime), month(starttime), count(*) from CMS_ARTICLE_FRONT where " + GetPubColl() + " and COLUMNID=" + cid + " group by year(starttime),month(starttime) order by year(starttime) desc,month(starttime) desc  ";

            SqlParameter[] parms = new SqlParameter[] { };
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            {
                while (rdr.Read())
                {

                    if (!rdr.IsDBNull(0) && !rdr.IsDBNull(1))
                    {
                        int year = rdr.GetInt32(0);
                        int month = rdr.GetInt32(1);
                        IList mlist;
                        if (map.ContainsKey(year))
                        {
                            mlist = (IList)map[year];
                        }
                        else
                        {
                            mlist = new ArrayList();
                        }
                        mlist.Add(month);

                        map[year] = mlist;

                    }
                }
            }
            return map;
        }

        public Hashtable GetColumnGroupYear(int cid)
        {

            Hashtable map = new Hashtable();
            string sql = "select year(ADDTIME), month(ADDTIME), count(*) from CMS_COLUMN where PARENTID=" + cid + " group by year(ADDTIME),month(ADDTIME) order by year(ADDTIME) desc,month(ADDTIME) desc  ";

            SqlParameter[] parms = new SqlParameter[] { };
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, sql, parms))
            {
                while (rdr.Read())
                {

                    if (!rdr.IsDBNull(0) && !rdr.IsDBNull(1))
                    {
                        int year = rdr.GetInt32(0);
                        int month = rdr.GetInt32(1);
                        IList mlist;
                        if (map.ContainsKey(year))
                        {
                            mlist = (IList)map[year];
                        }
                        else
                        {
                            mlist = new ArrayList();
                        }
                        mlist.Add(month);

                        map[year] = mlist;

                    }
                }
            }
            return map;
        }
        /// <summary>
        /// 相关单位查询
        /// </summary>
        /// <returns></returns>
        public IList GetSubsidiary(int aid)
        {
            string GetArticlesubsql = " and columnid in (select cc.classid from cms_column cc where cc.layerid like '1,2,3,18,99%') and ARTICLEID in (select article_id from CMS_ARTICLE_MORE where  ','+SUBSIDIARY+',' like ',%" + aid + "%,')";
            return GetArticleList(GetArticlesubsql, 0, " order by columnid,order_num");
        }


        //public List<BrandInfoModel> GetBusBrands(int aid)
        //{
        //    List<BrandInfoModel> datalist = new List<BrandInfoModel>();
        //    string qsql = "select b.BRAND_ID,b.BRAND_NAME,b.BRAND_LOGO from cofco_brand_info b where STATE=1 and b.brand_id in (select bc.brand_id from COFCO_BRAND_OTHER bc where ','+bus_ids+',' like '%," + aid + ",%') order by b.ORDER_NUM";
        //    using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, qsql, new SqlParameter[]{}))
        //    {
        //        while (rdr.Read())
        //        {
        //            BrandInfoModel bm = new BrandInfoModel();
        //            bm.Brandid = rdr.GetInt32(0);
        //            bm.Brandname = rdr.GetString(1);
        //            if (!rdr.IsDBNull(2)) bm.Brandlogo = rdr.GetString(2);
        //            datalist.Add(bm);
        //        }
        //    }

        //    return datalist;
        //}

    }
}
